<?php
namespace app\admin\model;
use think\Config;
use think\Db;
class BoardModel extends \app\common\model\CommonModel
{
    
    /**
     * 
     */
    public function info($data)
    {
        try {
            $today = date('Y-m-d');
            $date_s = !empty($data['date_s']) ? $data['date_s'] : date('Y-m-d 00:00:00');
            $date_e = !empty($data['date_e']) ? $data['date_e'] : date('Y-m-d 23:59:59');
            switch ($data['method']) {
                case 'online':
                    $start  = date("Y-m-d H:i:s",time()-60*5);
                    $end    = date('Y-m-d H:i:s');
                    $res = Db::table('user')->where("`active_time` BETWEEN '{$start}' AND '{$end}'")->count();
                    break;
                case 'users':
                    $res = Db::table('user')->count();
                    break;
                case 'tools':
                    $res = Db::table('tool')->count();
                    break;
                case 'usages':
                    $res = Db::table('usage')->count();
                    break;
                case 'todayusages':
                    $res = Db::table('usage')->where("DATE_FORMAT(`event_time`,'%Y-%m-%d') = '{$today}'")->count();
                    break;
                case 'mostusages':
                    $ret = Db::query("SELECT `id`,`name` FROM `tool` a INNER JOIN (SELECT count(*) `num`,`toolid` FROM `usage` WHERE DATE_FORMAT(`event_time`,'%Y-%m-%d') = '{$today}' GROUP BY `toolid` ORDER BY `num` DESC LIMIT 1) b ON a.id = b.toolid");
                    $res = !empty($ret[0]['name']) ? $ret[0]['name'] : '';
                    break;
                case 'mostcatusages':
                    $ret = Db::query("SELECT `id`,`cat` FROM `cat` a INNER JOIN (SELECT count(*) `num`,`catid` FROM `usage` WHERE DATE_FORMAT(`event_time`,'%Y-%m-%d') = '{$today}' GROUP BY `catid` ORDER BY `num` DESC LIMIT 1) b ON a.id = b.catid");
                    $res = !empty($ret[0]['cat']) ? $ret[0]['cat'] : '';
                    break;
                case 'mostusagesuser':
                    $ret = Db::query("SELECT a.`userid`,`username` FROM `user` a INNER JOIN (SELECT count(*) `num`,`userid` FROM `usage` WHERE DATE_FORMAT(`event_time`,'%Y-%m-%d') = '{$today}' GROUP BY `userid` ORDER BY `num` DESC LIMIT 1) b ON a.userid = b.userid");
                    $res = !empty($ret[0]['username']) ? $ret[0]['username'] : '';
                    break;
                case 'catrank':
                    $ret = Db::query("SELECT a.id catid,a.`cat`,(CASE WHEN b.`num` IS NOT NULL THEN b.`num` ELSE 0 END) as num FROM `cat` a LEFT JOIN 
                                        (SELECT count(*) `num`,`catid` FROM `usage` WHERE `event_time` BETWEEN '{$date_s}' AND '{$date_e}' GROUP BY `catid` ORDER BY `num` DESC) b 
                                        ON a.id = b.catid ORDER BY num DESC");
                    if($ret){
                        foreach ($ret as $key => $value) {
                            $ret[$key]['rank'] = $key + 1;
                        }
                    }
                    $days = getDateFromRange($date_s,$date_e);
                    $chart = [];
                    if($days){
                        $cats = Db::table('cat')->field("id catid,cat")->order('id desc')->select();
                        $chart['item'] = $cats;
                        foreach ($days as $key => $value) {
                            $item_ret = Db::query("SELECT a.id catid,a.`cat`,(CASE WHEN b.`num` IS NOT NULL THEN b.`num` ELSE 0 END) as num FROM `cat` a LEFT JOIN 
                                              (SELECT count(*) `num`,`catid` FROM `usage` WHERE DATE_FORMAT(`event_time`,'%Y-%m-%d') = '{$value}' GROUP BY `catid` ORDER BY `num` DESC) b 
                                              ON a.id = b.catid ORDER BY a.id DESC");    
                            $chart['info'][$key]['day'] = $value;        
                            $chart['info'][$key]['data'] = $item_ret;        
                        }
                    }
                    $res = [
                        'rank'  => $ret,
                        'chart' => $chart,
                    ];
                    break;
                case 'toolrank':
                    $ret = Db::query("SELECT a.id toolid,a.`name`,(CASE WHEN b.`num` IS NOT NULL THEN b.`num` ELSE 0 END) as num FROM `tool` a LEFT JOIN 
                                        (SELECT count(*) `num`,`toolid` FROM `usage` WHERE `event_time` BETWEEN '{$date_s}' AND '{$date_e}' GROUP BY `toolid` ORDER BY `num` DESC) b 
                                        ON a.id = b.toolid ORDER BY num DESC");
                    if($ret){
                        foreach ($ret as $key => $value) {
                            $ret[$key]['rank'] = $key + 1;
                        }
                    }
                    $days = getDateFromRange($date_s,$date_e);
                    $chart = [];
                    if($days){
                        $tools = Db::table('tool')->field("id toolid,name")->order('id desc')->select();
                        $chart['item'] = $tools;
                        foreach ($days as $key => $value) {
                            $item_ret = Db::query("SELECT a.id toolid,a.`name`,(CASE WHEN b.`num` IS NOT NULL THEN b.`num` ELSE 0 END) as num FROM `tool` a LEFT JOIN 
                                              (SELECT count(*) `num`,`toolid` FROM `usage` WHERE DATE_FORMAT(`event_time`,'%Y-%m-%d') = '{$value}' GROUP BY `toolid` ORDER BY `num` DESC) b 
                                              ON a.id = b.toolid ORDER BY a.id DESC");    
                            $chart['info'][$key]['day'] = $value;        
                            $chart['info'][$key]['data'] = $item_ret;        
                        }
                    }
                    $res = [
                        'rank'  => $ret,
                        'chart' => $chart,
                    ];
                    break;
                case 'companyrank':
                    $ret = Db::query("SELECT a.`company`,(CASE WHEN b.`num` IS NOT NULL THEN b.`num` ELSE 0 END) as num FROM 
                                        (SELECT DISTINCT(`company`) as company FROM `user` ORDER BY CONVERT(company using utf8) collate utf8_unicode_ci ASC) a 
                                        LEFT JOIN (SELECT count(*) num,d.company FROM `usage` c LEFT JOIN `user` d ON c.userid = d.userid 
                                        WHERE `event_time` BETWEEN '{$date_s}' AND '{$date_e}' GROUP BY d.company) b ON a.company = b.company");
                    if($ret){
                        foreach ($ret as $key => $value) {
                            $ret[$key]['rank'] = $key + 1;
                        }
                    }
                    $days = getDateFromRange($date_s,$date_e);
                    $chart = [];
                    if($days){
                        $company = Db::query("SELECT DISTINCT(`company`) as company FROM `user` ORDER BY CONVERT(company using utf8) collate utf8_unicode_ci ASC");
                        $chart['item'] = $company;
                        foreach ($days as $key => $value) {
                            $item_ret = Db::query("SELECT a.`company`,(CASE WHEN b.`num` IS NOT NULL THEN b.`num` ELSE 0 END) as num FROM 
                                                   (SELECT DISTINCT(`company`) as company FROM `user` ORDER BY CONVERT(company using utf8) collate utf8_unicode_ci ASC) a 
                                                   LEFT JOIN (SELECT count(*) num,d.company FROM `usage` c LEFT JOIN `user` d ON c.userid = d.userid 
                                                   WHERE DATE_FORMAT(`event_time`,'%Y-%m-%d') = '{$value}' GROUP BY d.company) b ON a.company = b.company");
                            $chart['info'][$key]['day'] = $value;        
                            $chart['info'][$key]['data'] = $item_ret;
                        }    
                    }
                    $res = [
                        'rank'  => $ret,
                        'chart' => $chart,
                    ];
                    break;
                case 'userrank':
                    $ret = Db::query("SELECT a.userid,a.`username`,b.`num` FROM `user` a INNER JOIN (SELECT count(*) `num`,`userid` FROM `usage` WHERE `event_time` 
                                      BETWEEN '{$date_s}' AND '{$date_e}' GROUP BY `userid` ORDER BY `num` DESC) b ON a.userid = b.userid");
                    if($ret){
                        foreach ($ret as $key => $value) {
                            $ret[$key]['rank'] = $key + 1;
                        }
                    }
                    $days = getDateFromRange($date_s,$date_e);
                    $chart = [];
                    if($days){
                        $user = Db::query("SELECT `username` FROM `user` ORDER BY CONVERT(username using utf8) collate utf8_unicode_ci ASC");
                        $chart['item'] = $user;
                        foreach ($days as $key => $value) {
                            $item_ret = Db::query("SELECT a.`username`,(CASE WHEN b.`num` IS NOT NULL THEN b.`num` ELSE 0 END) as num FROM 
                                                   (SELECT `username` FROM `user` ORDER BY CONVERT(username using utf8) collate utf8_unicode_ci ASC) a 
                                                   LEFT JOIN (SELECT count(*) num,d.username FROM `usage` c LEFT JOIN `user` d ON c.userid = d.userid 
                                                   WHERE DATE_FORMAT(`event_time`,'%Y-%m-%d') = '{$value}' GROUP BY d.username) b ON a.username = b.username 
                                                   ORDER BY num DESC");
                            $chart['info'][$key]['day'] = $value;        
                            $chart['info'][$key]['data'] = $item_ret;    
                        }    
                    }
                    $res = [
                        'rank'  => $ret,
                        'chart' => $chart,
                    ];
                    break;
                default:
                    throw new \Exception("参数错误");
                    break;
            }
            return ['result' => $res];
        } catch (\Exception $e) {
            throw new \Exception($e->getMessage());
        }
    }
}